"""
创建数据库连接
"""
import pymysql
import wind

import os
import configparser

# 读取配置文件  必须重启服务才可以重新加载
config_path = os.path.split(os.path.realpath(__file__))[0]
config = configparser.RawConfigParser()
config.read(config_path+'\config.ini', encoding="utf-8-sig")


class db:
    def __init__(self, host='', port='', user='', password='', db='', charset='', link_type=1):
        """
        link_type = 0 时，数据库返回结果为元组模式
        当link_type !=0时，数据库返回结果为字典模式
        :param host:
        :param port:
        :param user:
        :param password:
        :param db:
        :param charset:
        :param link_type:
        """
        try:
            self.host = config['mysql']['host']
            self.port = int(config['mysql']['port'])
            self.user = config['mysql']['user']
            self.password = config['mysql']['password']
            self.db = config['mysql']['db']
            self.charset = config['mysql']['charset']
            # print('db_start')
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,db=self.db, charset=self.charset)
            if link_type == 1:
                self.cur = self.conn.cursor(pymysql.cursors.DictCursor)
            else:
                self.cur = self.conn.cursor()
        except Exception as e:

            result = {'code': '9999', 'message': 'Mysql数据库连接失败', 'data': []}

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)

            return result
            # mylogger.info('Mysql数据库连接失败！')

    def select_one(self, params):
        """
        查询一条数据
        :param params:
        :return:
        """
        try:
            self.cur.execute(params)
            result = self.cur.fetchone()
            result = {'code': '0000', 'message': '执行单条查询操作成功', 'data': result}
        except Exception as e:
            result = {'code': '9999', 'message': '执行单条查询异常', 'data': []}

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)
            # mylogger.info('Mysql查询失败！')
        return result

    def select_all(self, params):
        """
        查询所有内容
        :param params:
        :return:
        """
        try:
            row = self.cur.execute(params)
            if row > 0:
                self.cur.scroll(0, mode='absolute')
                results = self.cur.fetchall()
                result = {'code': '0000', 'message': '执行查询所有数据成功', 'data': results}
            else:
                result = {'code': '0000', 'message': '执行查询所有数据成功', 'data': []}
        except Exception as e:
            result = {'code': '9999', 'message': '执行查询所有数据异常', 'data': []}

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)
            # mylogger.info('Mysql查询多条数据失败！')
        return result

    def opt_one(self, params):
        """
        操作单条数据
        :param params:
        :return:
        """
        try:
            row = self.cur.execute(params)


            #最新的id
            lastrowid = self.cur.lastrowid

            self.conn.commit()
            # print(params)

            if params[:12] == 'insert into ':

                result = {'code': '0000', 'message': '执行单条数据成功', 'data': int(lastrowid)}

            else:
                result = {'code': '0000', 'message': '执行单条数据成功', 'data': row}

        except Exception as e:

            result = {'code': '9999', 'message': '执行单条数据失败', 'data': []}
            print(e)
            # mylogger.info('Mysql操作单条数据失败')

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)
        return result

    def opt_many(self, stmt, data):
        """
        多条数据
        :param stmt:
        :param data:
        :return:
        """
        try:
            row = self.cur.executemany(stmt, data)
            self.conn.commit()
            result = {'code': '0000', 'message': '插入多条数据成功', 'data': int(row)}
        except Exception as e:
            result = {'code': '9999', 'message': '插入多条数据失败', 'data': []}
            # mylogger.info('Mysql 插入多条数据失败')

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)

        return result


    def opt_trans(self, param):
        """
        如果用到事务 请使用该方法
        :param param: 列表
        :return:
        """
        try:
            resType = isinstance(param,list)

            if resType == False:
                result = {'code': '9999', 'message': '数据类型不正确', 'data': []}
                return result

            #1提交事务  0回滚事务
            flag = 1
            for i in param:
                try:
                    row = self.cur.execute(i)
                except:
                    # mylogger.info('Mysql操作单条数据失败')
                    flag = 0
                    break

            if flag == 1:
                self.conn.commit()
                result = {'code': '0000', 'message': '事务提交', 'data': int(row)}
            else:
                self.conn.rollback()
                result = {'code': '9999', 'message': '事务回滚', 'data': False}

        except Exception as e:
            self.conn.rollback()
            result = {'code': '9999', 'message': '异常，事务回滚', 'data': False}
            # mylogger.info('Mysql 插入多条数据失败')

            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)

        return result


    def table_field(self,table):
        #获取某张表的全部字段

        try:
            sql = 'select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA="{}" and table_name = "{}"'.format(self.db,table)

            result = self.select_all(sql)['data']

            field_str = ''

            for i in result:
                field_str = field_str + i['COLUMN_NAME'] + ','


            field_str = field_str.strip(',')

        except Exception as e:

            field_str = False
            # wind.wind().logAbnormal(0, "db", "数据库连接类",e.__traceback__.tb_frame.f_globals["__file__"], e.__traceback__.tb_lineno, e)

        return field_str


    def __del__(self):
        # print('db_end')
        try:
            # if self.cur != None:
            self.cur.close()
            # if self.conn != None:
            self.conn.close()
        except Exception as e:
            print(e)



    def dbclose(self):
        try:
            self.cur.close()

            self.conn.close()
        except Exception as e:
            print('手动关闭：'+e)

# if __name__ == "__main__":
#     conn = pymysqlopreations(host='192.168.3.35', port=3306, user='star', password='star', db='ycjposition',
#                              link_type=1)
    # sql = " SELECT * FROM `interface_detail`"
    # resutl = conn.select_one(sql)
    # print(resutl)
    # sql_demo = """ INSERT INTO `interface_detail`(NAME,detail,param,check_value,exe_env,LEVEL,require_login) VALUES('xx','xx','xx','id=10',0,0,0)"""
    # roe = conn.opt_one(sql_demo)
    # print(roe)
    # results = conn.select_all(sql)
    # print(results)
    # updatestmt = ''' update `interface_detail` set LEVEL = %s where interface_id =%s'''
    # data = [(5,18)]
    # conn.opt_many(updatestmt,data)
    # results = conn.select_all(sql)
    # print(results)
